'''
Author: Jarvis
Date: 2021-10-29 18:08:44
LastEditTime: 2021-11-02 15:40:44
FilePath: /excel2-json/excel2python.py
Description: 
'''


import xlrd
import json
import codecs
import sys
import openpyxl


def Log(msg):
    print('Print Message: '+msg+' ,File: "'+__file__+'", Line '+str(sys._getframe().f_lineno)+' , in '+sys._getframe().f_code.co_name)


#把excel表格中指定sheet转为json
def Excel2Json(file_path):
    #打开excel文件
    if get_data(file_path) is not None:
        book = get_data(file_path)
        #抓取所有sheet页的名称
        worksheets = book.sheet_names()
        print("该Excel包含的表单列表为：\n")
        for sheet in worksheets:
            print ('%s,%s' %(worksheets.index(sheet),sheet))
        inp = raw_input(u'请输入表单名对应的编号，对应表单将自动转为json:\n')
        sheet = book.sheet_by_index(int(inp))
        row_0 = sheet.row(0)     #第一行是表单标题
        nrows=sheet.nrows       #行号
        ncols=sheet.ncols       #列号
 
 
        result={}   #定义json对象
        result["title"]=file_path   #表单标题
        result["rows"]=nrows        #行号
        result["children"]=[]      #每一行作为数组的一项
        #遍历所有行，将excel转化为json对象
        for i in range(nrows):
            if i==0:
                continue
            tmp={}
            #遍历当前行所有列
            for j in range(ncols):
                #获取当前列中文标题
                title_de=str(row_0[j]).decode('unicode_escape')
                title_cn= title_de.split("'")[1]
                #获取单元格的值
                tmp[title_cn]=sheet.row_values(i)[j]
            result["children"].append(tmp)
        json_data=json.dumps(result,indent= 4,sort_keys=True).decode('unicode_escape')
        
        saveFile(os.getcwd(),worksheets[int(inp)],json_data)
        print(json_data)
 

def Excel2Json_openpyxl(file_path):
    wb=openpyxl.load_workbook(file_path)#读取excel文件
    # sheet = wb.get_sheet_by_name("测试表格2007")
    sheet = wb["sheet1"]  #通过sheet名称锁定表格
    # new_list =[]
    article_info = {}
    data = json.loads(json.dumps(article_info))
    channel = {}
    sensor = {}
    channel_back = None
    sensortype_back = None

    for row in sheet.rows :  #循环所有的行
        if row[0].row < 3 :
            continue
        else :
            if row[2].value == None:
                break
            list_row = []
            for cell in row:#循环行中所有的单元格
                if cell.column < 2 :
                    continue
                else:
                    if cell.value == None:
                        break
                    # print(cell.value, "\t", end="")  #获取单元格的值
                    list_row.append(cell.value)
            print()
        datajson = pase2json(list_row)
        if sensortype_back != row[1].value:
            sensortype_back = row[1].value
            sensor.clear()
        sensor[row[2].value] = datajson
        print("=============================")
        print(row[2].value)
        print(row[1].value)
        print("============================================================")
        print(sensor)
        data[row[1].value] = sensor.copy()
        print(data)
        
    # 写入data文件
    with open('data.json','w') as f:
        json.dump(data,f,ensure_ascii=False,indent=4)

    
def pase2json(list_row):
    article_info = {}
    data = json.loads(json.dumps(article_info))
    # 1.sensor type
    # 2.value str
    struct_list = list_row[3].split(',')
    value = {
        "value":struct_list[0],
        "resolution":struct_list[1],
        "unit":struct_list[2],
        "status":struct_list[3]
    }

    # 3.get cmd
    get_cmd = list_row[5].replace(" ","")
    get = {
        "valid" : list_row[4],
        "cmd"   : json.dumps(list_row[5]),
        "arg"   : json.dumps(list_row[6]),
        "ok"    : list_row[7],
        "error" : list_row[8]
    }

    set = {
        "valid" : list_row[9],
        "cmd"   : json.dumps(list_row[10]),
        "arg"   : json.dumps(list_row[11]),
        "ok"    : list_row[12],
        "error" : list_row[13]
    }
    
    # 4.channel
    channel = {
        "value"     : value,
        "get"       : get,
        "set"       : set
    }

    # 5.cahnel
    data[list_row[1]] = channel
    article = json.dumps(data, ensure_ascii=False)    
    # print(article)
    return channel

    

# 获取excel数据源
def get_data(file_path):
    """获取excel数据源"""
    try:
        data = xlrd.open_workbook(file_path)
        return data
    except Exception as err:
        print('excel表格读取失败：%s',err)
        return None
 
def saveFile(file_path,file_name,data):
    output = codecs.open(file_path+"/"+file_name+".json",'w',"utf-8")
    output.write(data)
    output.close()
 
if __name__ == '__main__':
    # file_path = input('请输入excel文件路径：\n')
    file_path = sys.argv[1]
    print(file_path)
    Log('hahaha')
    # json_data=Excel2Json(file_path)
    Excel2Json_openpyxl(file_path)